#!/usr/bin/env python3
import pymysql
import argparse
from datetime import datetime

# MySQL 配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'Zhj1030222323@',
    'database': 'mydb',
    'cursorclass': pymysql.cursors.DictCursor
}

# 插入记录
def insert_stock(stock_name, stock_code, market, highest_price, lowest_price, reason):
    connection = pymysql.connect(**db_config)
    try:
        with connection.cursor() as cursor:
            sql = """INSERT INTO check_prices (stock_name, stock_code, market, highest_price, lowest_price, reason)
                     VALUES (%s, %s, %s, %s, %s, %s)"""
            cursor.execute(sql, (stock_name, stock_code, market, highest_price, lowest_price, reason))
        connection.commit()
        print("✅ 新纪录插入成功")
    finally:
        connection.close()

# 查询所有纪录
def query_stocks():
    connection = pymysql.connect(**db_config)
    try:
        with connection.cursor() as cursor:
            cursor.execute("SELECT * FROM check_prices")
            results = cursor.fetchall()
            if not results:
                print("表中没有数据。")
                return

            headers = list(results[0].keys())
            col_widths = [len(h) for h in headers]
            for row in results:
                for i, value in enumerate(row.values()):
                    col_widths[i] = max(col_widths[i], len(str(value)))

            header_row = " | ".join([f"{h:<{col_widths[i]}}" for i, h in enumerate(headers)])
            print(header_row)
            print("-" * len(header_row))

            for row in results:
                row_str = " | ".join([f"{str(v):<{col_widths[i]}}" for i, v in enumerate(row.values())])
                print(row_str)
    finally:
        connection.close()

# 更新记录
def update_stock(stock_id, stock_name, stock_code, market, highest_price, lowest_price, reason):
    connection = pymysql.connect(**db_config)
    try:
        with connection.cursor() as cursor:
            updates = []
            params = []
            if stock_name:
                updates.append("stock_name=%s")
                params.append(stock_name)
            if stock_code:
                updates.append("stock_code=%s")
                params.append(stock_code)
            if market:
                updates.append("market=%s")
                params.append(market)
            if highest_price:
                updates.append("highest_price=%s")
                params.append(highest_price)
            if lowest_price:
                updates.append("lowest_price=%s")
                params.append(lowest_price)
            if reason:
                updates.append("reason=%s")
                params.append(reason)

            if updates:
                sql = f"UPDATE check_prices SET {', '.join(updates)}, updated_at=%s WHERE id=%s"
                params.append(datetime.now())
                params.append(stock_id)
                cursor.execute(sql, tuple(params))
                connection.commit()
                print("✅ 记录更新成功")
            else:
                print("⚠️ 没有输入更新字段")
    finally:
        connection.close()

# 删除记录
def delete_stock(stock_id):
    connection = pymysql.connect(**db_config)
    try:
        with connection.cursor() as cursor:
            cursor.execute("DELETE FROM stocks WHERE id=%s", (stock_id,))
        connection.commit()
        print(f"🗑️ ID={stock_id} 的记录已删除")
    finally:
        connection.close()

# 命令行入口
def main():
    parser = argparse.ArgumentParser(description="Stock Manager")
    subparsers = parser.add_subparsers(dest="command")

    insert_parser = subparsers.add_parser("insert")
    insert_parser.add_argument("--stock_name", required=True)
    insert_parser.add_argument("--stock_code", required=True)
    insert_parser.add_argument("--market", required=True)
    insert_parser.add_argument("--highest_price", type=float, required=False)
    insert_parser.add_argument("--lowest_price", type=float, required=False)
    insert_parser.add_argument("--reason", required=True)

    subparsers.add_parser("query")

    update_parser = subparsers.add_parser("update")
    update_parser.add_argument("--id", type=int, required=True)
    update_parser.add_argument("--stock_name")
    update_parser.add_argument("--stock_code")
    update_parser.add_argument("--market")
    update_parser.add_argument("--highest_price", type=float)
    update_parser.add_argument("--lowest_price", type=float)
    update_parser.add_argument("--reason")

    delete_parser = subparsers.add_parser("delete")
    delete_parser.add_argument("--id", type=int, required=True)

    args = parser.parse_args()

    if args.command == "insert":
        insert_stock(args.stock_name, args.stock_code, args.market,
                     args.highest_price, args.lowest_price, args.reason)
    elif args.command == "query":
        query_stocks()
    elif args.command == "update":
        update_stock(args.id, args.stock_name, args.stock_code, args.market,
                     args.highest_price, args.lowest_price, args.reason)
    elif args.command == "delete":
        delete_stock(args.id)
    else:
        parser.print_help()

if __name__ == "__main__":
    main()
